﻿using PersistentObject.Common;
using PersistentObject.Common.Schema;
using PersistentObject.Common.Sorts;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PersistentObject.SqlServer2008R2
{
    public class SqlCreatorForSqlServer2008R2 : BaseSqlCreator
    {
        public SqlCreatorForSqlServer2008R2(Table mainTable, BaseDialect dialect)
            :base(mainTable, dialect)
        {

        }

        public SqlCreatorForSqlServer2008R2(Type type, BaseDialect dialect)
            : base(type, dialect)
        {
        }


        public override string CreateSelectSql(Common.Criteria.BaseCriteria criteria, Common.Sorts.OrderBy[] orderBys, int pageSize, int pageIndex)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from (").AppendLine();

            sb.Append("select").AppendLine();
            var selectColumnLines = GetSelectClauseLines();
            sb.Append(selectColumnLines);
            sb.Append(",row_number() over (order by ");

            if (orderBys != null && orderBys.Count() > 0)
            {
                BaseOrderByCreator orderByCreator = new BaseOrderByCreator(MainTable, this.Dialect);
                for (int i = 0; i < orderBys.Count(); i++)
                {
                    OrderBy orderBy = orderBys[i];
                    var orderByClause = orderByCreator.GetOrderByClause(orderBy);
                    if (i > 0)
                    {
                        sb.Append(",");
                    }
                    sb.Append(orderByClause);
                }
                sb.AppendLine();
            }
            else
            {
                sb.Append("(select 1)").AppendLine();
            }

            sb.Append(") as [__PAGING__]").AppendLine();
            sb.Append("from").AppendLine();
            var fromTableLines = GetFromClauseLines();
            sb.Append(fromTableLines);

            if (criteria != null)
            {
                BaseCriteriaCreator criteriaCreator = new BaseCriteriaCreator(MainTable, this.Dialect);
                var criteriaClause = criteriaCreator.GetCriteriaClause(criteria);
                sb.Append("where ").AppendLine();
                sb.Append(criteriaClause).AppendLine();
            }
            sb.Append(")X").AppendLine();

            var pagingClause = string.Format("where [__PAGING__] between {0} and {1}", pageIndex * pageSize + 1, (pageIndex+1) * pageSize);
            sb.Append(pagingClause).AppendLine();

            //sb.Append(";");
            var sql = sb.ToString();
            return sql;
        }
    }
}
